﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLiNhaSachDTO;
namespace QuanLiNhaSachDAO
{
    public class NhanVienDAO
    {
        public static DataTable selectAll()
        {
            string sql = "SELECT id as [Mã], ten as [Họ Tên], ngaysinh as [Ngày Sinh], CMND, gioitinh as [Giới Tính], sodienthoai as [Số Điện Thoại], diachi as [Địa Chỉ], Email, UserName, Password, loai as [Loại] FROM NHANVIEN";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable login(string username, string password)
        {
            string sql = "SELECT * FROM NHANVIEN WHERE Username='"+username+"' AND Password='"+password+"'";
            
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static void insert(NhanVien m)
        {
            string sql = "INSERT INTO NHANVIEN(Ten,NgaySinh,CMND,GioiTinh,SoDienThoai,DiaChi,Email,Username,Password,Loai) VALUES(@Ten,@NgaySinh,@CMND,@GioiTinh,@SoDienThoai,@DiaChi,@Email,@Username,@Password,@Loai)";
            SqlCommand cmd = new SqlCommand(sql, sqlDB.MyConnection);
            
            cmd.Parameters.AddWithValue("@Ten", m.Ten);
            cmd.Parameters.AddWithValue("@NgaySinh", m.NgaySinh.ToShortDateString());
            cmd.Parameters.AddWithValue("@CMND", m.CMND);
            cmd.Parameters.AddWithValue("@GioiTinh", m.GioiTinh);
            cmd.Parameters.AddWithValue("@SoDienThoai", m.SoDienThoai);
            cmd.Parameters.AddWithValue("@DiaChi", m.DiaChi);
            cmd.Parameters.AddWithValue("@Email", m.Email);
            cmd.Parameters.AddWithValue("@Username", m.Username);
            cmd.Parameters.AddWithValue("@Password", m.Password);
            cmd.Parameters.AddWithValue("@Loai", m.Loai);        
            cmd.ExecuteNonQuery();
            
        }

        public static void update(NhanVien m)
        {
            string sql = "UPDATE NHANVIEN SET Ten = @Ten, NgaySinh = @NgaySinh, CMND = @CMND, GioiTinh = @GioiTinh, SoDienThoai = @SoDienThoai, DiaChi = @DiaChi, Email = @Email, Username = @Username, Password = @Password, Loai = @Loai WHERE id = @id";
            SqlCommand cmd = new SqlCommand(sql, sqlDB.MyConnection);
            
            cmd.Parameters.AddWithValue("@Ten", m.Ten);
            cmd.Parameters.AddWithValue("@NgaySinh", m.NgaySinh.ToShortDateString());
            cmd.Parameters.AddWithValue("@CMND", m.CMND);
            cmd.Parameters.AddWithValue("@GioiTinh", m.GioiTinh);
            cmd.Parameters.AddWithValue("@SoDienThoai", m.SoDienThoai);
            cmd.Parameters.AddWithValue("@DiaChi", m.DiaChi);
            cmd.Parameters.AddWithValue("@Email", m.Email);
            cmd.Parameters.AddWithValue("@Username", m.Username);
            cmd.Parameters.AddWithValue("@Password", m.Password);
            cmd.Parameters.AddWithValue("@Loai", m.Loai);
            cmd.Parameters.AddWithValue("@id", m.id);           
            cmd.ExecuteNonQuery();
            
        }

        public static void delete(string id)
        {
            string sql = "DELETE FROM NHANVIEN WHERE id = @id";
            SqlCommand cmd = new SqlCommand(sql, sqlDB.MyConnection);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
        }
    }
}
